package com.valentinalee.bms.dao.impl;

import java.security.interfaces.RSAKey;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.valentinalee.bms.dao.FileSystemDAO;
import com.valentinalee.bms.entity.FileSystemVO;
import com.valentinalee.bms.util.DBUtil;
import com.valentinalee.bms.util.ThisAppUtil;

public class FileSystemDAOImpl implements FileSystemDAO{

	public FileSystemVO addFileRmark(FileSystemVO vo) throws SQLException {
		// TODO Auto-generated method stub
		Connection con=DBUtil.getConnection();//创建数据库连接
		String sql="insert into t_filesystem(fileuser,filename,filepath,handletime,handleflag) values(?,?,?,?,?)";
		try{
			PreparedStatement ps=con.prepareStatement(sql);
			int i=1;
			ps.setString(i++,vo.getFileUser());
			ps.setString(i++, vo.getFileName());
			ps.setString(i++, vo.getFilePath());
			ps.setString(i++, vo.getHandleTime());
			ps.setString(i++, vo.getHandleFlag());
			ps.executeUpdate();
			//提交事务
			con.commit();
		}catch(SQLException e){
			//事务回滚
			con.rollback();
			throw e;
		}finally{
			DBUtil.close(con);
		}
		return vo;
	}

	public int queryByKey(String key, int pageSize, int pageNo,
			List<FileSystemVO> pageData) throws SQLException {
		int totalCount=0;
		Connection con=DBUtil.getConnection();
		StringBuilder countSQL=new StringBuilder();
		countSQL.append("select count(*) from t_filesystem ");
		if(!ThisAppUtil.isNone(key)){
			countSQL.append(" where ")
			.append(" handleflag = ?  ");
		}
		PreparedStatement ps=con.prepareStatement(countSQL.toString());
		if(!ThisAppUtil.isNone(key)){
			
			ps.setString(1, key);
		}
		ResultSet rs=ps.executeQuery();
		if(rs.next()){
			if((totalCount=rs.getInt(1))==0){
				return 0;
			}
		}
		ps.close();
		StringBuilder selectSQL=new StringBuilder();
		selectSQL.append("select * from ")
		.append("t_filesystem");
		if(!ThisAppUtil.isNone(key)){
			selectSQL.append(" where ")
			.append(" handleflag like ?  ");
		}
		selectSQL.append(" limit ").append((pageNo-1)*pageSize).append(",")
		.append(pageSize);
		try{
			ps=con.prepareStatement(selectSQL.toString());
			if(!ThisAppUtil.isNone(key)){
				key="%"+key+"%";
				ps.setString(1, key);
			}
			rs=ps.executeQuery();
			
			while(rs.next()){
				FileSystemVO vo=new FileSystemVO();
				vo.setFileUser(rs.getString("fileuser"));
				vo.setFileName(rs.getString("filename"));
				vo.setFilePath(rs.getString("filepath"));
				vo.setHandleTime(rs.getString("handletime"));
				vo.setHandleFlag(rs.getString("handleflag"));
				vo.setId(rs.getString("id"));
				pageData.add(vo);
			}
		}finally{
			DBUtil.close(con);
		}
		return totalCount;
	}

	public FileSystemVO setFileRmark(FileSystemVO vo) throws SQLException {
		Connection con=DBUtil.getConnection();//创建数据库连接
		vo.setId(queryFileSystemVOByFileName(vo).getId());
		String sql="update t_filesystem set handleflag=? where id=?";
		try{
			PreparedStatement ps=con.prepareStatement(sql);
			int i=1;
			ps.setString(i++,vo.getHandleFlag());
			ps.setString(i++, vo.getId());
			ps.executeUpdate();
			//提交事务
			con.commit();
		}catch(SQLException e){
			//事务回滚
			con.rollback();
			throw e;
		}finally{
			DBUtil.close(con);
		}
		return vo;
	}

	public FileSystemVO queryFileSystemVOByFileName(FileSystemVO vo)
			throws SQLException {
		Connection con=DBUtil.getConnection();
		FileSystemVO vos=new FileSystemVO();
		StringBuilder selectSQL=new StringBuilder();
		selectSQL.append("select id from ")
		.append(" t_filesystem ").append(" where filename='"+vo.getFileName()+"' and handleflag='u' ");
		PreparedStatement ps =null;
		ResultSet rs = null;
		try{
			ps=con.prepareStatement(selectSQL.toString());
			
			rs=ps.executeQuery();
			
			while(rs.next()){
				
				vos.setId(String.valueOf(rs.getInt("id")));
				
			}
		}finally{
			DBUtil.close(con);
		}
		return vos;
	}

	public FileSystemVO FileQuery(String id) {
		FileSystemVO vo=new FileSystemVO();
		Connection connection =null;
		try {
			 connection = DBUtil.getConnection();
			StringBuilder sb = new  StringBuilder();
			sb.append("SELECT id,fileuser,filename FROM `t_filesystem` where id="+id);
			PreparedStatement ps = connection.prepareStatement(sb.toString());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				vo.setId(rs.getString("id"));
				vo.setFileUser(rs.getString("fileuser"));
				vo.setFileName(rs.getString("filename"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				DBUtil.close(connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return vo;
	}

	public Integer updateFileUserById(String fileUser, String id) {
		String sql="update t_filesystem set fileuser='"+fileUser+"'  where id="+id;
		Connection con=null;
		try {
			con = DBUtil.getConnection();
			PreparedStatement ps = con.prepareStatement(sql);
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			try {
				DBUtil.close(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public FileSystemVO getFileInfoById(String id) {
		
		FileSystemVO vo=new FileSystemVO();
		
		//1.定义sql
		String sql="SELECT * FROM `t_filesystem`  where id="+id;
		Connection connection=null;
		//2.创建连接
		try {
			 connection = DBUtil.getConnection();
			PreparedStatement ps = connection.prepareStatement(sql);
			
			//得到一个结果的集合
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				vo.setId(rs.getString("id"));
				vo.setFileUser(rs.getString("fileuser"));
				vo.setFileName(rs.getString("filename"));
				vo.setFilePath(rs.getString("filepath"));
				return vo;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				DBUtil.close(connection);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return vo;
	}

}
